﻿namespace UnitFundProfitability.DataMigration
{
	using System;
	using System.Configuration;
	using System.Data;
	using System.Data.EntityClient;
	using System.Data.SqlClient;
	using System.Data.SqlServerCe;
	using System.IO;
	using System.Reflection;
	using System.Text.RegularExpressions;

	/// <summary>
	/// Helps to migrate or create database.
	/// </summary>
	public static class DataMigrationHelper
	{
		private const string NullDatabase = "0.0.0.0";

		/// <summary>
		/// Migrates the data in database in specified database file to latest schema version.
		/// </summary>
		/// <param name="databaseFileName">Name of the database file.</param>
		public static void Migrate(string databaseFileName)
		{
			if (string.IsNullOrWhiteSpace(databaseFileName))
			{
				throw new ArgumentException("Database file name should not be empty.", "databaseFileName");
			}
			string databaseVersion = DefineDatabaseVersion(databaseFileName);
			if (databaseVersion.Equals(NullDatabase, StringComparison.OrdinalIgnoreCase))
			{
				Assembly executingAssembly = Assembly.GetExecutingAssembly();
				using (SqlCeConnection connection = CreateConnection())
				{
					using (Stream resourceStream = executingAssembly.GetManifestResourceStream(typeof(DataMigrationHelper), "database.sdf"))
					{
						using (FileStream databaseFile = new FileStream(connection.DataSource, FileMode.Create, FileAccess.Write))
						{
							resourceStream.CopyTo(databaseFile);
						}
					}
				}
			}
			string databasePatch = GetDatabasePatch(databaseVersion);
			while (!string.IsNullOrWhiteSpace(databasePatch))
			{
				ApplyPatch(databasePatch);
				databaseVersion = DefineDatabaseVersion(databaseFileName);
				databasePatch = GetDatabasePatch(databaseVersion);
			}
		}

		/// <summary>
		/// Applies the patch.
		/// </summary>
		/// <param name="databasePatch">The database patch.</param>
		private static void ApplyPatch(string databasePatch)
		{
			using (SqlCeConnection connection = CreateConnection())
			{
				foreach (string singleCommand in Regex.Split(databasePatch, @"\s+go\s+", RegexOptions.IgnoreCase | RegexOptions.CultureInvariant))
				{
					if (!string.IsNullOrWhiteSpace(singleCommand))
					{
						using (SqlCeCommand command = connection.CreateCommand())
						{
							command.CommandText = singleCommand;
							if (connection.State != ConnectionState.Open)
							{
								connection.Open();
							}
							command.ExecuteNonQuery();
						}
					}
				}
			}
		}

		/// <summary>
		/// Gets the database patch.
		/// </summary>
		/// <param name="databaseVersion">The database version.</param>
		/// <returns>Database patch.</returns>
		private static string GetDatabasePatch(string databaseVersion)
		{
			return GetResourceAsString(string.Concat("Patches.", databaseVersion, ".sqlce"));
		}

		/// <summary>
		/// Defines the database version.
		/// </summary>
		/// <param name="databaseFileName">Name of the database file.</param>
		/// <returns>Database version.</returns>
		private static string DefineDatabaseVersion(string databaseFileName)
		{
			string result = null;
			if (!File.Exists(databaseFileName))
			{
				result = NullDatabase;
			}
			else
			{
				using (SqlCeConnection connection = CreateConnection())
				{
					bool isDbConfigExists;
					using (SqlCeCommand command = connection.CreateCommand())
					{
						command.CommandText = GetResourceAsString("CheckIfDbConfigExists.sqlce");
						connection.Open();
						int count = (int)command.ExecuteScalar();
						isDbConfigExists = count == 1;
					}
					if (isDbConfigExists)
					{
						using (SqlCeCommand command = connection.CreateCommand())
						{
							command.CommandText = GetResourceAsString("GetDatabaseVerstion.sqlce");
							result = command.ExecuteScalar() as string;
						}
					}
					else
					{
						result = DefineDatabaseVersionUsingIsPendingOperationColumn(connection);
					}
				}
			}
			return result;
		}

		/// <summary>
		/// Defines the database version using is pending operation column.
		/// </summary>
		/// <param name="connection">The connection.</param>
		/// <returns>Database version.</returns>
		private static string DefineDatabaseVersionUsingIsPendingOperationColumn(SqlCeConnection connection)
		{
			string result;
			bool isPendinOperationExists;
			using (SqlCeCommand command = connection.CreateCommand())
			{
				command.CommandText = GetResourceAsString("CheckIfIsPendingOperationExists.sqlce");
				if (connection.State != ConnectionState.Open)
				{
					connection.Open();
				}
				int count = (int)command.ExecuteScalar();
				connection.Close();
				isPendinOperationExists = count == 1;
			}
			if (isPendinOperationExists)
			{
				result = "1.1.0.0";
			}
			else
			{
				result = "1.0.0.0";
			}
			return result;
		}

		/// <summary>
		/// Gets the resource as string.
		/// </summary>
		/// <param name="resourceName">Name of the resource.</param>
		/// <returns>Resource as string.</returns>
		private static string GetResourceAsString(string resourceName)
		{
			string result = string.Empty;
			Assembly executingAssembly = Assembly.GetExecutingAssembly();
			Stream resourceStream = executingAssembly.GetManifestResourceStream(typeof(DataMigrationHelper), resourceName);
			if (resourceStream != null)
			{
				using (StreamReader resourceReader = new StreamReader(resourceStream))
				{
					result = resourceReader.ReadToEnd();
				}
			}
			return result;
		}

		/// <summary>
		/// Creates the connection.
		/// </summary>
		/// <returns>Sql connection.</returns>
		private static SqlCeConnection CreateConnection()
		{
			using (EntityConnection entityConnection = new EntityConnection(ConfigurationManager.ConnectionStrings["UnitFundsEntities"].ConnectionString))
			{
				SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
				connectionStringBuilder.DataSource = entityConnection.DataSource;
				return new SqlCeConnection(connectionStringBuilder.ConnectionString);
			}
		}
	}
}
